package com.lecture.java8.excel.dyr.d;

import com.alibaba.excel.EasyExcel;
import com.lecture.java8.excel.DemoDataListener;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test0511 {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String dir = "D:\\study\\tmp\\0511\\";
        List<Model0511> list = new ArrayList<>();

        String result1 = dir + "6副本政府干预.xls";
        Map<String,String> map = new HashMap<>();
        List<Map<String, Object>> excelData1 = readExcel(result1);
        for (Map<String, Object> row : excelData1) {
            for (Map.Entry<String, Object> entry : row.entrySet()) {
                map.put(entry.getKey(),entry.getValue()+"");
            }
            System.out.println("------------");
        }

        String result6 = dir + "result4.xlsx";
        DemoDataListener<Model0511> resultDataListener6 = new DemoDataListener<>();
        EasyExcel.read(result6, Model0511.class,resultDataListener6).sheet().doRead();
        //全要素生产率
        List<Model0511> resultList6 = resultDataListener6.getDataList();
        for (Model0511 model0511 : resultList6) {
            model0511.setH(map.get(model0511.getA()));
        }
        EasyExcel.write(dir + "result5.xlsx", Model0511.class)
                .sheet("a").doWrite(resultList6);
    }
    public static void main3(String[] args) {
        String dir = "D:\\study\\tmp\\0511\\";




        /*String result1 = dir + "1大学生人数.xls";
        DemoDataListener<Map<String,Object>> dataResultListener = new DemoDataListener<>();
        EasyExcel.read(result1, dataResultListener).sheet().doRead();
        List<Map<String, Object>> dataList = dataResultListener.getDataList();
        System.out.println(dataList.size());

        String result2 = dir + "2人均分省年度数据.xls";
        DemoDataListener<Map<String,Object>> dataResultListener2 = new DemoDataListener<>();
        EasyExcel.read(result2, dataResultListener2).sheet().doRead();
        List<Map<String, Object>> dataList2 = dataResultListener.getDataList();

        Map<String,Model0511> resultMap = new HashMap<>();

        for (Map<String, Object> map : dataList) {
            map.forEach((k,v) -> {

            });
        }*/
        try {

            Map<String,Model0511> resultMap = new HashMap<>();

            String result1 = dir + "1大学生人数.xls";
            List<Map<String, Object>> excelData1 = readExcel(result1);
            for (Map<String, Object> row : excelData1) {
                String key = "";
                for (Map.Entry<String, Object> entry : row.entrySet()) {
                    if (!entry.getKey().contains("年")) {
                        key = entry.getValue()+"";
                        continue;
                    }
                    String sKey = key+"-" + entry.getKey();
                    if (resultMap.get(sKey) != null) {
                        Model0511 model = resultMap.get(sKey);
                        model.setC(entry.getValue()+"");
                    } else {
                        Model0511 model0511 = new Model0511();
                        model0511.setA(entry.getKey());
                        model0511.setB(key);
                        model0511.setC(entry.getValue()+"");
                        resultMap.put(sKey,model0511);
                    }
                }
                System.out.println("------------");
            }


            String result2 = dir + "2人均分省年度数据.xls";
            List<Map<String, Object>> excelData2 = readExcel(result2);
            for (Map<String, Object> row : excelData2) {
                String key = "";
                for (Map.Entry<String, Object> entry : row.entrySet()) {
                    if (!entry.getKey().contains("年")) {
                        key = entry.getValue()+"";
                        continue;
                    }
                    String sKey = key+"-" + entry.getKey();
                    if (resultMap.get(sKey) != null) {
                        Model0511 model = resultMap.get(sKey);
                        model.setD(entry.getValue()+"");
                    } else {
                        Model0511 model0511 = new Model0511();
                        model0511.setA(entry.getKey());
                        model0511.setB(key);
                        model0511.setD(entry.getValue()+"");
                        resultMap.put(sKey,model0511);
                    }
                }
                System.out.println("------------");
            }

            String result5 = dir + "430个省份2007-2021年制造业碳排放数据.xlsx";
            DemoDataListener<Model0511_1> resultDataListener5 = new DemoDataListener<>();
            EasyExcel.read(result5, Model0511_1.class,resultDataListener5).sheet().doRead();
            //全要素生产率
            List<Model0511_1> resultList5 = resultDataListener5.getDataList();
            Map<String,Model0511_1> map5 = new HashMap<>();
            for (Model0511_1 result : resultList5) {
                String key = result.getA() +"-" + result.getB()+"年";
                Model0511 model0511 = resultMap.get(key);
                if (model0511 == null) {
                    model0511 = new Model0511();
                    model0511.setA(result.getB());
                    model0511.setB(result.getA());
                    model0511.setE(result.getC());
                } else {
                    model0511.setE(result.getC());
                }

                map5.put(key,result);
            }




            String result6 = dir + "5人工智能专利.xlsx";
            DemoDataListener<Model0511_2> resultDataListener6 = new DemoDataListener<>();
            EasyExcel.read(result6, Model0511_2.class,resultDataListener6).sheet().doRead();
            //全要素生产率
            List<Model0511_2> resultList6 = resultDataListener6.getDataList();
            Map<String,Model0511_2> map6 = new HashMap<>();
            for (Model0511_2 result : resultList6) {
                String key = result.getA() +"省-" + result.getB()+"年";
                String key2 = result.getA() +"市-" + result.getB()+"年";
                String key3 = result.getA() +"-" + result.getB()+"年";
                map6.put(key,result);
                map6.put(key2,result);
                map6.put(key3,result);

                Model0511 model0511 = resultMap.get(key);
                if (model0511 == null) {
                    model0511 = resultMap.get(key2);
                    if (model0511 == null) {
                        model0511 = resultMap.get(key3);
                    }
                }
                if (model0511 == null) {
                    model0511 = new Model0511();
                    model0511.setA(result.getB());
                    model0511.setB(result.getA());
                    model0511.setF(result.getC());
                } else {
                    model0511.setF(result.getC());
                }

            }

            List<Model0511> resultList = new ArrayList<>();
            resultMap.forEach((k,v) -> {
                resultList.add(v);
            });
            EasyExcel.write(dir + "result2.xlsx", Model0511.class)
                    .sheet("a").doWrite(resultList);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static List<Map<String, Object>> readExcel(String excelFilePath) throws IOException, InvalidFormatException {
        List<Map<String, Object>> excelData = new ArrayList<>();

        FileInputStream inputStream = new FileInputStream(excelFilePath);
        Workbook workbook = WorkbookFactory.create(inputStream);

        Sheet sheet = workbook.getSheetAt(0); // 假设你想要读取第一个工作表

        Row headerRow = sheet.getRow(0);
        int rowCount = sheet.getPhysicalNumberOfRows();

        for (int rowIndex = 1; rowIndex < rowCount; rowIndex++) {
            Row currentRow = sheet.getRow(rowIndex);
            Map<String, Object> rowMap = new HashMap<>();

            for (int cellIndex = 0; cellIndex < headerRow.getLastCellNum(); cellIndex++) {
                Cell currentCell = currentRow.getCell(cellIndex);
                CellType cellType = CellType.forInt(currentCell.getCellType());

                String columnName = headerRow.getCell(cellIndex).getStringCellValue();

                switch (cellType) {
                    case STRING:
                        rowMap.put(columnName, currentCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        rowMap.put(columnName, currentCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        rowMap.put(columnName, currentCell.getBooleanCellValue());
                        break;
                    case BLANK:
                        rowMap.put(columnName, ""); // 如果是空白单元格，以空字符串表示
                        break;
                    default:
                        // 对于其他类型的单元格，你可以根据需要进行处理
                        rowMap.put(columnName, "");
                }
            }
            excelData.add(rowMap);
        }

        workbook.close();
        inputStream.close();

        return excelData;
    }
}
